<?php


namespace app\web\controller;
use app\common\model\ViperCard as Viper_Card_Model;
use think\Db;
use think\Validate;

/**
 * 会员卡报表分析记录表
 * @package app\index\controller
 */
class ViperCardReport extends Auth
{
    public $noNeedLogin = '';

    /**
     * 会员卡分析
     */
    public function analysis()
    {
        if ($this->request->isPost()) {
            $getData = $this->request->post();
            $sql = Db::name('qt_check_out_pay')
                ->alias('pay')
                ->leftJoin('qt_viper_card c','c.id = pay.viper_card_id')
                ->field('c.id,c.mobile,c.card_no,c.name,c.amount,c.give_amount')
                ->whereBetweenTime('pay.create_time', strtotime($getData['start_time']), strtotime($getData['end_time']))
                ->where('pay.viper_card_id','neq','0');
            $map[] = ['pay.create_time', 'between', [strtotime($getData['start_time']), strtotime($getData['end_time'])]];
            $time[] = ['create_time', 'between', [strtotime($getData['start_time']), strtotime($getData['end_time'])]];
            //充值金额查询
            if (!empty($getData['recharge_money'])) {
                $sql->where('pay.pay_money', '>', $getData['recharge_money']);
                $sql->whereIn('pay.type',[1,3]);
                $map[] = ['pay.pay_money', '>', $getData['recharge_money']];
                $map[] = ['pay.type', 'in', ['1','3']];
            }
            //消费金额查询
            if (!empty($getData['consumption_money'])) {
                $sql->where('pay.pay_money', '>', $getData['consumption_money']);
                $sql->where(['pay.type' => '2']);
                $map[] = ['pay.money', '>', $getData['consumption_money']];
                $map[] = ['pay.type', '=', '2'];
            }
            //消费次数查询
            if (!empty($getData['consumption_count'])) {
                $sql->where(['pay.type' => '2'])
                    ->having('count(pay.viper_card_id)'.'>'.$getData['consumption_count']);
            }
            $data = $sql->group('pay.viper_card_id')->paginate($getData['limit'])->each(function ($item) use ($map,$time) {
                //充值金额
                $item['recharge_money'] = Db::name('qt_check_out_pay')
                    ->alias('pay')
                    ->leftJoin('qt_viper_card c', 'pay.viper_card_id = c.id')
                    ->where($map)
                    ->where(['pay.viper_card_id'=>$item['id']])
                    ->whereIn('pay.type',[1,3])
                    ->sum('pay_money');
                //赠送金额
                $item['recharge_give_money'] = Db::name('qt_viper_card_recharge_log')
                    ->where($time)
                    ->where(['viper_card_id'=>$item['id']])
                    ->sum('give_amount');
                //消费金额
                $item['consumption_money'] = Db::name('qt_check_out_pay')
                    ->alias('pay')
                    ->leftJoin('qt_viper_card c', 'pay.viper_card_id = c.id')
                    ->where($map)
                    ->where(['pay.viper_card_id'=>$item['id']])
                    ->whereIn('pay.type',[2])
                    ->sum('pay_money');
                //消费次数
                $item['consumption_count'] = Db::name('qt_check_out_pay')
                    ->alias('pay')
                    ->leftJoin('qt_viper_card c', 'pay.viper_card_id = c.id')
                    ->where($map)
                    ->where(['pay.viper_card_id'=>$item['id']])
                    ->whereIn('pay.type',[2])
                    ->count();
                return $item;
            });
            $this->result('查询成功', $data, 200);
        }
    }


    /**
     * 会员卡余额变更分析
     */
    public function balance_analysis()
    {
        if ($this->request->isPost()) {
            $getData = $this->request->post();
            $sql = Db::name('qt_check_out_pay')
                ->alias('pay')
                ->leftJoin('qt_viper_card c', 'pay.viper_card_id = c.id')
                ->field('c.id,c.mobile,c.card_no,c.name,c.amount,c.give_amount')
                ->where('pay.viper_card_id','neq','0')
                ->whereBetweenTime('pay.create_time', strtotime($getData['start_time']), strtotime($getData['end_time']));
            //会员卡类型筛选
            if (!empty($getData['viper_type_id'])) {
                $sql->where('c.viper_type_id', $getData['viper_type_id']);
            }
            //会员卡手机筛选
            if (!empty($getData['mobile'])) {
                $sql->where('c.mobile', $getData['mobile']);
            }
            $map[] = ['log.create_time', 'between', [strtotime($getData['start_time']), strtotime($getData['end_time'])]];
            $where[0] = $map;
            $where[1][] = ['create_time', 'between', [strtotime($getData['start_time']), strtotime($getData['end_time'])]];
            $data = $sql->group('pay.viper_card_id')
                ->paginate($getData['limit'])->each(function ($item) use ($where) {
                //充值金额
                $item['recharge_money'] = Db::name('qt_check_out_pay')
                    ->where(['viper_card_id'=>$item['id']])
                    ->whereIn('type' ,[1,3])
                    ->where($where[1])
                    ->sum('pay_money');
                //赠送金额
                $item['recharge_give_money'] = Db::name('qt_check_out_pay')
                    ->where(['viper_card_id'=>$item['id']])
                    ->whereIn('type',[1,3])
                    ->where($where[1])
                    ->sum('card_give_amount');
                //消费金额
                $item['consumption_money'] = Db::name('qt_check_out_pay')
                    ->where(['viper_card_id'=>$item['id']])
                    ->whereIn('type' ,[2])
                    ->where($where[1])
                    ->sum('pay_money');
                //消费赠送金额
                $item['consumption_give_money'] = Db::name('qt_check_out_pay')
                    ->where(['viper_card_id'=>$item['id']])
                    ->whereIn('type' ,[2])
                    ->where($where[1])
                    ->sum('card_give_money');
                //开始金额
                $stareMoney = Db::name('qt_check_out_pay')
                    ->where($where[1])
                    ->where(['viper_card_id' => $item['id'],'type'=>'2'])
                    ->order('id asc')
                    ->value('card_amount');
                $item['start_money'] = $stareMoney ?? 0 ;
                if ($item['start_money'] == null) $item['start_money'] = 0;
                //开始赠送金额
                $startGiveMoney = Db::name('qt_check_out_pay')
                    ->where($where[1])
                    ->where(['viper_card_id' => $item['id'],'type'=>'2'])
                    ->order('id asc')
                    ->value('card_give_amount');
                $item['start_give_money'] = $startGiveMoney ?? 0;
                if ($item['start_give_money'] == null) $item['start_give_money'] = 0;
                //结束金额
                 $endData= Db::name('qt_check_out_pay')
                    ->field('card_amount,card_money')
                    ->where($where[1])
                    ->where(['viper_card_id' => $item['id']])
                    ->order('id desc')
                    ->find();
                if($endData['card_amount'] == 0){
                    $endMoney = $endData['pay_money'];
                }else{
                    $endMoney = $endData['card_amount'];
                }
                $item['end_money'] = $endMoney;
                //结束赠送金额
                $endGiveData = Db::name('qt_check_out_pay')
                    ->field('card_give_money,card_give_amount')
                    ->where($where[1])
                    ->where(['viper_card_id' => $item['id']])
                    ->order('id desc')
                    ->find();
                if($endGiveData['card_give_amount'] == null){
                    $endGiveMoney = $endGiveData['card_give_money'];
                }else{
                    $endGiveMoney = $endGiveData['card_give_amount'];
                }
                $item['end_give_money'] = $endGiveMoney;
                return $item;
            });
            $this->result('查询成功', $data, 200);
        }
    }

    /**
     * 会员卡沉默会员分析
     */
    public function silent()
    {
        if ($this->request->isPost()) {
            $getData = $this->request->post();
            $sql = Db::name('qt_viper_card')
                ->field('c.id,c.mobile,t.name as viper_type_name,c.name,c.total_amount,c.give_total_amount,c.consumption_amount,c.amount,c.score,c.status,c.end_consumption_time')
                ->alias('c')
                ->leftJoin('qt_viper_type t', 't.id = c.viper_type_id');
            //->whereBetweenTime('create_time',strtotime($getData['start_time']),strtotime($getData['end_time']));
            //会员卡类型筛选
            if (!empty($getData['viper_type_id'])) {
                $viperCardType = explode(',',$getData['viper_type_id']);
                $sql->whereIn('c.viper_type_id', $viperCardType);
            }
            //充值金额大于
            if (!empty($getData['total_amount'])) {
                $sql->where('c.total_amount', '>', $getData['total_amount']);
            }
            //消费金额大于
            if (!empty($getData['consumption_amount'])) {
                $sql->where('c.consumption_amount', '>', $getData['consumption_amount']);
            }
            //沉默时间大于
            if (!empty($getData['end_consumption_time'])) {
                $time = time() * 86400 * $getData['end_consumption_time'];
                $sql->where('c.end_consumption_time', '<', $time);
            }
            //生日
            if (!empty($getData['birthday_num ber'])) {
                $sql->whereLike('c.birthday_number','%'.$getData['birthday_number']);
            }
            //卡状态
            if (!empty($getData['status'])) {
                $statusIds = explode(',',$getData['status']);
                $sql->whereIn('c.status',$statusIds);
            }
            //余额
            if (!empty($getData['start_amount'])) {
                $sql->where('c.amount', '>', $getData['start_amount']);
            }
            if (!empty($getData['end_amount'])) {
                $sql->where('c.amount', '<', $getData['end_amount']);
            }
            if (!empty($getData['end_amount']) && !empty($getData['start_amount'])) {
                $sql->where('c.amount', '>', $getData['start_amount']);
                $sql->where('c.amount', '<', $getData['end_amount']);
            }
            $data = $sql->order('c.id desc')->paginate($getData['limit']);
            $this->result('查询成功', $data, 200);
        }
    }


    /**
     * 会员卡售卡明细列表
     */
    public function selling_cards()
    {
        if ($this->request->isPost()) {
            $getData = $this->request->post();
            $map[] = ['created_time', 'between', [strtotime($getData['start_time']), strtotime($getData['end_time'])]];
            $sql = Db::name('qt_commission_log')
                ->field('u.id,u.real_name,u.work_number,u.group,log.cashier_id,log.is_first,u.group')
                ->alias('log')
                ->leftJoin('qt_viper_card_recharge_log recharge','recharge.id = log.order_project_id')
                ->leftJoin('qt_users u','u.id = recharge.sale_id')
                ->whereNotNull('u.id')
                ->whereIn('log.type', ['3','4','5','7'])
                ->whereBetweenTime('log.created_time', strtotime($getData['start_time']), strtotime($getData['end_time']))
                ->group('recharge.sale_id');
            //员工分类查找
            if (!empty($getData['group'])) {
                $sql->where('u.group', $getData['group']);
            }
            //员工筛选
            if (!empty($getData['user_id'])) {
                $sql->where('u.id', $getData['user_id']);
            }
            $data = $sql->paginate($getData['limit'])->each(function ($item) use ($map) {
                //技师
                if($item['group'] == '1'){
                    $map[] = ['user_id','=',$item['id']];
                    //提成金额
                    $item['commission'] = Db::name('qt_commission_log')
                        ->where($map)
                        ->whereIn('type', ['3','4','5','7'])
                        ->sum('commission');
                }else{
                    $map[] = ['cashier_id','=',$item['id']];
                    //提成金额
                    $item['commission'] = Db::name('qt_commission_log')
                        ->where($map)
                        ->whereIn('type', ['3','4','5','7'])
                        ->sum('receiver');
                }
                //总张数
                $item['card_number'] = Db::name('qt_commission_log')
                    ->where($map)
                    ->whereIn('type', ['3','4','5','7'])
                    ->count();
                //充值金额
                $item['total_amount'] = Db::name('qt_commission_log')
                    ->where($map)
                    ->whereIn('type', ['3','4','5','7'])
                    ->sum('money');
                //首充
                $item['first_amount'] = Db::name('qt_commission_log')
                    ->where($map)
                    ->where(['is_first'=>'1'])
                    ->sum('money');
                //续充
                $item['recharge_amount'] = Db::name('qt_commission_log')
                    ->where($map)
                    ->where(['is_first'=>'2'])
                    ->whereIn('type', ['3','4','5','7'])
                    ->sum('money');
                //充值明细详情
                $item['card_detail'] = Db::name('qt_commission_log')
                    ->alias('log')
                    ->leftJoin('qt_viper_card_recharge_log rlog', 'rlog.id = log.order_project_id')
                    ->where($map)
                    ->whereIn('log.type', ['4', '3','5','7'])
                    ->column('rlog.recharge_temp_name');
                return $item;
            });
            $this->result('查询成功', $data, 200);
        }
    }



    /**
     * 会员卡售卡阶梯提成列表
     */
    public function selling_commission()
    {
        if ($this->request->isPost()) {
            $getData = $this->request->post();
            $map[] = ['log.created_time', 'between', [strtotime($getData['start_time']), strtotime($getData['end_time'])]];
            $sql = Db::name('qt_commission_log')
                ->field('u.id,u.real_name,u.work_number,u.group,log.cashier_id,r.name as role_name')
                ->alias('log')
                ->leftJoin('qt_users u', 'u.id = log.cashier_id')
                ->leftJoin('qt_user_role r', 'r.id = u.top_type_id')
                ->whereIn('log.type',['3','4','5','7'])
                ->whereBetweenTime('log.created_time', strtotime($getData['start_time']), strtotime($getData['end_time']))
                ->group('log.cashier_id');
//            $data = $sql->paginate($getData['limit']);
//            dump($data);
            //员工分类查找
            if (!empty($getData['top_type_id'])) {
                $sql->where('u.top_type_id', $getData['top_type_id']);
                $map[] = ['u.top_type_id', '=', $getData['top_type_id']];
            }
            //员工工号筛选
            if (!empty($getData['work_number'])) {
                $sql->where('u.work_number', $getData['work_number']);
                $map[] = ['u.work_number', '=', $getData['work_number']];
            }
            $data = $sql->paginate($getData['limit'])->each(function ($item) use ($map) {
                //总提成金额
                $item['total_commission'] = Db::name('qt_commission_log')
                    ->where(['cashier_id' => $item['cashier_id']])
                    ->whereIn('type',['3','4','5','7'])
                    ->sum('receiver');
                //总业绩
                $item['total_money'] = Db::name('qt_commission_log')
                    ->where(['cashier_id' => $item['cashier_id']])
                    ->whereIn('type',['3','4','5','7'])
                    ->sum('money');
                //总次卡月卡张数
                $item['card_number'] = Db::name('qt_commission_log')
                    ->where([ 'cashier_id' => $item['cashier_id']])
                    ->whereIn('type',['5','7'])
                    ->count();
                //总次卡月卡充值业绩
                $item['total_amount'] = Db::name('qt_commission_log')
                    ->where(['cashier_id' => $item['cashier_id']])
                    ->whereIn('type',['5','7'])
                    ->sum('money');
                //充值次数
                $item['recharge_count'] = Db::name('qt_commission_log')
                    ->where(['cashier_id' => $item['cashier_id']])
                    ->whereIn('type', ['4','3'])
                    ->count();
                //充值业绩
                $item['recharge_money'] = Db::name('qt_commission_log')
                    ->where(['cashier_id' => $item['cashier_id']])
                    ->whereIn('type', ['4','3'])
                    ->sum('money');
                return $item;
            });
            $this->result('查询成功', $data, 200);
        }
    }

    /**
     *会员卡消费详情
     */
    public function analysis_consumption_detail(){
        if($this->request->isPost()){
            $getData = $this->request->post();
            $rule = [
                'viper_card_id|会员卡' => 'require|number',
            ];
            // 定义验证提示
            $message = [
                'viper_card_id.require' => '会员卡不能为空',
                'viper_card_id.number' => '会员卡格式不正确',
            ];
            $validate = new Validate($rule, $message);
            if (!$validate->check($getData)) {
                $this->result($validate->getError(), '', 204);
            }
            $viperCardModel = new Viper_Card_Model();
            $result = $viperCardModel::getDetailList($getData['viper_card_id'],$getData['limit'],$getData['page']);
            $this->result('查询成功', $result, 200);
        }
        $this->result('无效的请求','' , 204);
    }


    /**
     *会员卡充值详情
     */
    public function analysis_recharge_detail(){
        if($this->request->isPost()){
            $getData = $this->request->post();
            $rule = [
                'viper_card_id|会员卡' => 'require|number',
            ];
            // 定义验证提示
            $message = [
                'viper_card_id.require' => '会员卡不能为空',
                'viper_card_id.number' => '会员卡格式不正确',
            ];
            $validate = new Validate($rule, $message);
            if (!$validate->check($getData)) {
                $this->result($validate->getError(), '', 204);
            }
            $viperCardModel = new Viper_Card_Model();
            $result = $viperCardModel::getRechargeList($getData['viper_card_id'],$getData['limit'],$getData['page']);
            $this->result('查询成功', $result, 200);
        }
        $this->result('无效的请求','' , 204);
    }


    /**
     * 会员售卡阶梯提成
     */
    public function stepCommission(){
        if($this->request->isPost()){
            $getData = $this->request->post();
            $rule = [
                'start_time|开始时间' => 'require',
                'end_time|结束时间' => 'require',
                'page|页码' => 'require',
                'limit|每页条数' => 'require',
            ];
            // 定义验证提示
            $message = [
                'start_time.require' => '请选择开始时间',
                'end_time.require' => '请选择结束时间',
                'page.require' => '没有分页',
                'limit.require' => '没有每页条数',
            ];
            $validate = new Validate($rule, $message);
            if (!$validate->check($getData)) {
                $this->result($validate->getError(), '', 204);
            }
            $sql = Db::name('qt_commission_log')
                ->field('sum(log.money) as total_money,count(log.id) as total_number,u.id,u.real_name,u.work_number,u.work_type,role.name as role_name,u.group,u.post_id')
                ->alias('log')
                ->leftJoin('qt_viper_card_recharge_log rlog','rlog.id = log.order_project_id')
                ->leftJoin('qt_users u','u.id = rlog.sale_id')
                ->leftJoin('qt_user_role role','role.id = u.post_id')
                ->whereNotNull('u.id')
                ->whereBetweenTime('log.created_time',strtotime($getData['start_time']),strtotime($getData['end_time']))
                ->whereIn('log.type',['3','4','5','7']);
            if(!empty($getData['work_number'])){
                $sql->where('u.work_number',$getData['work_number']);
            }
            if(!empty($getData['role'])){
                $sql->where('u.post_id',$getData['role']);
            }
            $data = $sql->group('log.user_id')
                ->paginate($getData['limit'])->each(function ($item) use ($getData){
                    if($item['group'] == '1'){
                        $map[] = ['user_id','=',$item['id']];
                    }else{
                        $map[] = ['cashier_id','=',$item['id']];
                    }
                    //次卡月卡业绩
                    $item['card_number_money'] = Db::name('qt_commission_log')
                        ->whereBetweenTime('created_time',strtotime($getData['start_time']),strtotime($getData['end_time']))
                        ->whereIn('type',['5','7'])
                        ->where($map)
                        ->sum('money');
                    //次卡月卡次数
                    $item['card_number_number'] = Db::name('qt_commission_log')
                        ->whereBetweenTime('created_time',strtotime($getData['start_time']),strtotime($getData['end_time']))
                        ->whereIn('type',['5','7'])
                        ->where($map)
                        ->count('id');
                    //充值次数
                    $item['card_recharge_number'] = Db::name('qt_commission_log')
                        ->whereBetweenTime('created_time',strtotime($getData['start_time']),strtotime($getData['end_time']))
                        ->whereIn('type',['4'])
                        ->where($map)
                        ->count('id');
                    //充值金额
                    $item['card_recharge_money'] = Db::name('qt_commission_log')
                        ->whereBetweenTime('created_time',strtotime($getData['start_time']),strtotime($getData['end_time']))
                        ->whereIn('type',['4'])
                        ->where($map)
                        ->sum('money');
                    $commission = \app\common\model\ViperCardConfig::getCommission($item['post_id'],$item['total_money'],$item['total_number']);
                    $item['total_commission'] = $commission;
                    return $item;
                });

            $this->result('查询成功', $data, 200);
        }
        $this->result('无效的请求','' , 204);
    }



}
